package booroo.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import booroo.model.MemberVO;
import booroo.utils.OracleJDBC;

public class MemberDAO {
	private final String insertSQL = "insert into memberv1(mno,name,userid,pwd,gender) values(mbseq.nextval,?,?,?,?)";
	private final String updateSQL = "update memberv1 set name=?,pwd=?,regdate=sysdate where mno=?";
	private final String deleteSQL = "delete from memberv1 where mno=?";
	private final String selectSQL = "select mno,userid,name,regdate from memberv1";
	private final String selectOneSQL = "select * from memberv1 where mno=?";
	
	public int insertMember(MemberVO m){
		
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try{
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(insertSQL);
			ps.setString(1, m.getName());
			ps.setString(2, m.getUserid());
			ps.setString(3, m.getPwd());
			ps.setString(4, m.getGender());
			cnt = ps.executeUpdate();
		}catch(Exception ex){}
		finally{
			OracleJDBC.closeConn(conn, ps, null);
		}
		
		return cnt;
	}
	
	public int updateMember(MemberVO m){
		
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try{
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(updateSQL);
			ps.setString(1, m.getName());
			ps.setString(2, m.getPwd());
			ps.setString(3, m.getMno());
			cnt = ps.executeUpdate();
		}catch(Exception ex){}
		finally{
			OracleJDBC.closeConn(conn, ps, null);
		}
		return cnt;
	}
	
	public int deleteMember(String mno){

		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try{
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(deleteSQL);
			ps.setString(1, mno);
			cnt = ps.executeUpdate();
		}catch(Exception ex){}
		finally{
			OracleJDBC.closeConn(conn, ps, null);
		}
		return cnt;
	}
	
	public ArrayList<MemberVO> selectMember(){
		ArrayList<MemberVO> list = new ArrayList<>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int cnt = 0;
		
		try{
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();
			while(rs.next()){
				MemberVO m = new MemberVO(rs.getString(1),rs.getString(2),rs.getString(3),null,null,rs.getString(4));
				list.add(m);
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
		finally{
			OracleJDBC.closeConn(conn, ps, rs);
		}
		return list;
	}
	
	public MemberVO selectOneMember(String mno){
		
		MemberVO m = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
				
		try{
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(selectOneSQL);
			ps.setString(1, mno);
			rs = ps.executeQuery();
			while(rs.next()){
				m = new MemberVO(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6));
								
			}
			}catch(Exception ex){ex.printStackTrace();}
		finally{
			OracleJDBC.closeConn(conn, ps, rs);
		}
		return m;
	}
}
